﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using HNQXYJ.Model;

namespace HNQXYJ.DAL
{
    public static  class TerminalDAL
    {
        public static DataTable GetTerminalTable(string procName, params SqlParameter[] paras)
        {

            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            return dt;

        }
        /// <summary>
        /// 向终端表中插入新数据
        /// </summary>
        /// <param name="procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int InsertTerminalData(string procname, params SqlParameter[] paras)
        {
            int res = SqlHelper.ExecNonQuery(procname, paras);
            return res;
        }

        public static int DeleteTerminalData(string procName, params SqlParameter[] paras)
        {
            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;
        }

        /// <summary>
        /// 通过存储过程执行所有的非查询语句
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paras">参数</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecNonQuery(string procName, params SqlParameter[] paras)
        {
            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;
        }

        /// <summary>
        /// 通过终端号码更改终端状态
        /// </summary>
        /// <param name="phone">终端号码</param>
        /// <param name="status">终端状态</param>
        public static int UpdateStatusByPhone(string phone, string status)
        {
            string procName = "TerminalUpdateStatusByPhone";
            SqlParameter[] paras = { 
                                  new SqlParameter("@Phone",SqlDbType.VarChar,50),
                                  new SqlParameter("@Statuss",SqlDbType.NVarChar,50)
                                  };
            paras[0].Value = phone;
            paras[1].Value = status;
            int res = ExecNonQuery(procName, paras);
            return res;
        }


        public static int GetNumberByStatus(string status)
        {
            string procName = "TerminalGetDataForTongji";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Statuss",SqlDbType.NVarChar,50)
                                   };
            paras[0].Value = status;

            int count = Convert.ToInt32(SqlHelper.ExecuteScalar(procName,paras));
            return count;
        }


        /// <summary>
        /// 统计出每个城市下的各个县市区的终端的状态数据
        /// </summary>
        /// <param name="status"></param>
        /// <param name="ranklevel"></param>
        /// <returns></returns>
        public static int GetNumberByStatusAndCounty(string status, string ranklevel,string category)
        {
            string procName = "TerminalGetDataByCountyForTongJi";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Statuss",SqlDbType.NVarChar,50),
                                   new SqlParameter("@RankLevel",SqlDbType.VarChar,100),
                                   new SqlParameter("@Category",SqlDbType.VarChar,50)
                                   };
            paras[0].Value = status;
            paras[1].Value = ranklevel;
            paras[2].Value = category;
            int count = Convert.ToInt32(SqlHelper.ExecuteScalar(procName,paras));
            return count;
        }

        /// <summary>
        /// 根据权限码统计出每个县市的总共的终端数
        /// </summary>
        /// <param name="ranklevel"></param>
        /// <returns>count</returns>
        public static int GetNumberALLByRankLevel(string ranklevel,string category)
        {
            string procName = "TerminalGetCountByRankLevelForTongJi";
            SqlParameter[] paras = { 
                                   new SqlParameter("@RankLevel",SqlDbType.VarChar,100),
                                   new SqlParameter("@Category",SqlDbType.VarChar,50)
                                   };
            paras[0].Value=ranklevel;
            paras[1].Value = category;
            int count=Convert.ToInt32(SqlHelper.ExecuteScalar(procName,paras));
            return count;
        }




        //返回终端模型对象 
        private static Terminal ToModel(DataRow dr)
        {
            Terminal tm = new Terminal();
            tm.ID =Convert.ToInt32( dr["Id"]);
            tm.Phone = dr["Phone"].ToString();
            tm.TownName = dr["TownName"].ToString();
            tm.AreaCode = dr["AreaCode"].ToString();
            tm.Leader = dr["Leader"].ToString();
            tm.LeaderPhone = dr["LeaderPhone"].ToString();
            
            tm.Statuss = dr["Statuss"].ToString();
            tm.Lng = Convert.ToDouble(dr["Lng"]);
            tm.Lat = Convert.ToDouble(dr["Lat"]);
            tm.RankLevel = dr["RankLevel"].ToString();
            tm.Category = dr["Category"].ToString();
            tm.AddDate = dr["AddDate"].ToString().Split(' ')[0];
            tm.Notes = dr["Notes"].ToString();
            return tm;
        }

        /// <summary>
        /// 根据权限吗返回一个终端列表
        /// </summary>
        /// <param name="ranklevel"></param>
        /// <returns></returns>
        public static List<Terminal> GetTerminlListByRankLevel(string ranklevel)
        {
            List<Terminal> list = new List<Terminal>();
            string procName = "TerminalGetDataByRankLevel";
            SqlParameter[] paras = { 
                                   new SqlParameter("@RankLevel",SqlDbType.VarChar,100)
                                   };
            paras[0].Value = ranklevel;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            foreach (DataRow row in dt.Rows)
            {
                list.Add(ToModel(row));
            }
            return list;
 
        }

        /// <summary>
        /// 向终端表插入数据
        /// </summary>
        /// <param name="tm"></param>
        public  static int InsertTerminalData(Terminal tm)
        {
            int res=0;
            string procName="TerminalInserIntoData";
            SqlParameter [] paras ={
                                   new SqlParameter("@Phone",SqlDbType.VarChar,50),
                                   new SqlParameter("@TownName",SqlDbType.NVarChar,50),
                                   new SqlParameter("@AreaCode",SqlDbType.VarChar,50),
                                   new SqlParameter("@Leader",SqlDbType.NVarChar,50),
                                   new SqlParameter("@LeaderPhone",SqlDbType.VarChar,50),
                                   new SqlParameter("@Statuss",SqlDbType.VarChar,50),
                                   new SqlParameter("@Lng",SqlDbType.Float),
                                   new SqlParameter("@Lat",SqlDbType.Float),
                                   new SqlParameter("@RankLevel",SqlDbType.VarChar,100),
                                   new SqlParameter("@Category",SqlDbType.VarChar,50),
                                   new SqlParameter("@AddDate",SqlDbType.DateTime),
                                   new SqlParameter("@Notes",SqlDbType.NVarChar,500)
                                   };
            paras[0].Value=tm.Phone;
            paras[1].Value=tm.TownName;
            paras[2].Value=tm.AreaCode;
            paras[3].Value=tm.Leader;
            paras[4].Value=tm.LeaderPhone;
            paras[5].Value=tm.Statuss;
            paras[6].Value=tm.Lng;
            paras[7].Value=tm.Lat;
            paras[8].Value=tm.RankLevel;
            paras[9].Value=tm.Category;
            paras[10].Value=tm.AddDate;
            paras[11].Value=tm.Notes;

            res=SqlHelper.ExecNonQuery(procName,paras);
            return res;
        }

        /// <summary>
        /// 根据当前输入的页数和页容量大小来取数据
        /// </summary>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public static List<Terminal> GetDataByPage(int pageNumber, int pageSize)
        {
            List<Terminal> list = new List<Terminal>();
            string procName = "TerminalGetDataForPaged";
            SqlParameter[] paras = {
                                   new SqlParameter("@pageNumber",SqlDbType.Int),
                                   new SqlParameter("@pageSize",SqlDbType.Int )
                                   };
            paras[0].Value = pageNumber;
            paras[1].Value = pageSize;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);

            foreach (DataRow row in dt.Rows)
            {
                list.Add(ToModel(row));
            }
            return list;
        }
        
        /// <summary>
        /// 获取终端表的总条数
        /// </summary>
        /// <returns></returns>
        public static int  GetTerminalAllCount()
        {
            int rowCount = 0;
            string procName = "TerminalGetDataForPagedCount";
            rowCount = Convert.ToInt32(SqlHelper.ExecuteScalar(procName));
            return rowCount;

        }

        /// <summary>
        /// 专为泗阳做的终端号码提取方法。
        /// </summary>
        /// <returns></returns>
        public static List<string> GetPhoneForSendMsgToSiYang()
        {
            string procName = "TerminalJSGetPhone";
            DataTable dt = SqlHelper.GetDataTable(procName);
            List<string> list=new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(dr["Phone"].ToString().Trim());
            }
            return list;

        }

        /// <summary>
        /// 分页存储过程
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="rowCount">总行数</param>
        /// <param name="pageCount">总页数</param>
        /// <returns></returns>
        public static List<Terminal> TerminalPagingList(int pageIndex, int pageSize,string strWhere, out int rowCount, out int pageCount)
        {
            rowCount = 1;
            pageCount = 1;
            string procName = "TerminalPaging";
            SqlParameter[] paras = { 
                                   new SqlParameter("@PageIndex",SqlDbType.Int),
                                   new SqlParameter("@PageSize",SqlDbType.Int),
                                   new SqlParameter("@strWhere",SqlDbType.NVarChar,100),
                                   new SqlParameter("@RowCount",SqlDbType.Int),
                                   new SqlParameter("@PageCount",SqlDbType.Int)

                                   };
            paras[0].Value = pageIndex;
            paras[1].Value = pageSize;
            paras[2].Value = strWhere;
            paras[3].Value = rowCount;
            paras[3].Direction = ParameterDirection.Output;
            paras[4].Value = pageSize;
            paras[4].Direction = ParameterDirection.Output;

            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            if (dt.Rows.Count > 0)
            {
                List<Terminal> list = new List<Terminal>();
                foreach (DataRow row in dt.Rows)
                {
                    list.Add(ToModel(row));

                }
                rowCount = Convert.ToInt32(paras[3].Value);
                pageCount = Convert.ToInt32(paras[4].Value);
                return list;
            }
            else
            {
                rowCount = 0;
                pageCount = 0;
                return null;
            }

        }

        /// <summary>
        /// 带权限的分页存储过程
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">也容量</param>
        /// <param name="townName">村镇名称</param>
        /// <param name="rankLevel">权限码</param>
        /// <param name="rowCount">总行数</param>
        /// <param name="pageCount">总页数</param>
        /// <returns>list</returns>
        public static List<Terminal> TerminalPagedRankLevel(int pageIndex, int pageSize,string townName,string rankLevel,out int rowCount,out int pageCount)
        {

            rowCount = 0; 
            pageCount=0;
            string procName = "TerminalPagingRankLevel";
            SqlParameter[] paras = {
                                   new SqlParameter("@PageIndex",SqlDbType.Int),
                                   new SqlParameter("@PageSize",SqlDbType.Int),
                                   new SqlParameter("@TownName",SqlDbType.NVarChar,50),
                                   new SqlParameter("@RankLevel",SqlDbType.VarChar,100),
                                   new SqlParameter("@RowCount",SqlDbType.Int),
                                   new SqlParameter("@PageCount",SqlDbType.Int)
                                   };
            paras[0].Value = pageIndex;
            paras[1].Value = pageSize;
            paras[2].Value = townName;
            paras[3].Value = rankLevel;
            paras[4].Value = rowCount;
            paras[4].Direction = ParameterDirection.Output;
            paras[5].Value = pageSize;
            paras[5].Direction = ParameterDirection.Output;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            if (dt.Rows.Count > 0)
            {
                List<Terminal> list = new List<Terminal>();
                foreach (DataRow row in dt.Rows)
                {
                    list.Add(ToModel(row));

                }
                rowCount = Convert.ToInt32(paras[4].Value);
                pageCount = Convert.ToInt32(paras[5].Value);
                return list;
            }
            else
            {
                rowCount = 0;
                pageCount = 0;
                return null;
            }
        }

        /// <summary>
        /// 根据权限码来获取终端号码
        /// </summary>
        /// <param name="ranklevel"></param>
        /// <returns></returns>
        public static List<string> GetPhonesByRankLevel(string ranklevel)
        {
            string procName = "TerminalGetPhonesByRankLevel";
            SqlParameter[] paras = { 
                                   new SqlParameter("@RankLevel",SqlDbType.VarChar,100)
                                   };
            paras[0].Value = ranklevel;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            List<string> list = new List<string>();
            foreach(DataRow  dr in dt.Rows)
            {
                list.Add(dr["Phone"].ToString().Trim());
            }
            return list;
            

        }

        /// <summary>
        /// 根据分组代码提取终端号码
        /// </summary>
        /// <param name="areacode"></param>
        /// <returns></returns>
         public static List<string> GetPhonesByAreaCode(string areacode)
        {
            string procName = "TerminalGetPhonesByAreaCode";
            SqlParameter[] paras = { 
                                   new SqlParameter("@AreaCode",SqlDbType.VarChar,100)
                                   };
            paras[0].Value = areacode;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            List<string> list = new List<string>();
            foreach(DataRow  dr in dt.Rows)
            {
                list.Add(dr["Phone"].ToString().Trim());
            }
            return list;           
        }

        /// <summary>
        /// 通过终端号码来更新其所在分组
        /// </summary>
        /// <param name="phone"></param>
        /// <param name="areaCode"></param>
        /// <returns></returns>
         public static  int UpateAreaCodeByPhone(string phone, string areaCode)
         {
             string procName = "TerminalUpdateByAreaCode";
             SqlParameter[] paras = { 
                                    new SqlParameter("@Phone",SqlDbType.VarChar,50),
                                    new SqlParameter("@AreaCode",SqlDbType.VarChar,100)
                                    };
             paras[0].Value = phone;
             paras[1].Value = areaCode;
             int res = SqlHelper.ExecNonQuery(procName, paras);
             return res;
         }

        /// <summary>
        /// 根据id获取一个终端对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
         public static Terminal GetDataById(int id)
         {
             string procName = "TerminalGetDataById";
             SqlParameter[] paras = { 
                                    new SqlParameter("@Id",SqlDbType.Int)
                                    };
             paras[0].Value = id;
             DataTable dt = SqlHelper.GetDataTable(procName, paras);
             DataRow dr = dt.Rows[0];
             Terminal tm = ToModel(dr);
             return tm;
         }

         /// <summary>
         /// 更新一个终端
         /// </summary>
         /// <param name="tm"></param>
         /// <returns></returns>
         public static int UpdateTerminalById(Terminal tm)
         {
             string procName = "TerminalUpdateById";
             SqlParameter[] paras = { 
                                     new SqlParameter("@Id",SqlDbType.Int),
                                     new SqlParameter("@Phone",SqlDbType.VarChar,50),
                                     new SqlParameter("@TownName",SqlDbType.NVarChar,50),
                                     new SqlParameter("@AreaCode",SqlDbType.VarChar,100),
                                     new SqlParameter("@Leader",SqlDbType.NVarChar,50),
                                     new SqlParameter("@LeaderPhone",SqlDbType.VarChar,50),
                                     new SqlParameter("@Statuss",SqlDbType.NVarChar,50),
                                     new SqlParameter("@Lng",SqlDbType.Float),
                                     new SqlParameter("@Lat",SqlDbType.Float),
                                     new SqlParameter("@RankLevel",SqlDbType.VarChar,100),
                                     new SqlParameter("@Category",SqlDbType.NVarChar,50),
                                     new SqlParameter("@Notes",SqlDbType.NVarChar,500)
                                    };
             paras[0].Value = tm.ID;
             paras[1].Value = tm.Phone;
             paras[2].Value = tm.TownName;
             paras[3].Value = tm.AreaCode;
             paras[4].Value = tm.Leader;
             paras[5].Value = tm.LeaderPhone;
             paras[6].Value = tm.Statuss;
             paras[7].Value = tm.Lng;
             paras[8].Value = tm.Lat;
             paras[9].Value = tm.RankLevel;
             paras[10].Value = tm.Category;
             paras[11].Value = tm.Notes;

             int res = SqlHelper.ExecNonQuery(procName, paras);
             return res;
         }

         ////返回终端模型对象 
         private static Terminal ToModelSecond(DataRow dr)
         {
             Terminal tm = new Terminal();
             tm.Phone = dr["Phone"].ToString();
             tm.Statuss = dr["Statuss"].ToString();
             return tm;
         }

         /// <summary>
         /// 返回终端列表 只有终端号码和状态
         /// </summary>
         /// <returns></returns>
         public static List<Terminal> GetPhoneAndStatus()
         {
             string procName = "TerminalGetPhoneAndStatus";

             DataTable dt = SqlHelper.GetDataTable(procName);
             if (dt.Rows.Count > 0)
             {
                 List<Terminal> list = new List<Terminal>();
                 foreach (DataRow dr in dt.Rows)
                 {
                     list.Add(ToModelSecond(dr));
                 }
                 return list;
             }
             else
             {
                 return null;
             }

         }

    }
}